SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query from 3 tables in 1

    Hello,

    I am trying to query specific information from 3 tables, the only relationship in the data is that it was selected from the same form.

    here is my table info

    Table: cities
    columns: cityID, cityName, cityState

    Table: users
    columns: userID, userName, userEmail

    Table: jobs
    columns: jobID, jobName, jobStart


    Individually the queries would be:

    select cityName from cities where cityID = '1'
    select userName, userEmail from users where userID = '53'
    select jobName from jobs where jobID='2'

    Is there a way to combine these into one query?

    I will be displaying the results back using PHP.

    Thank You.

  2. #2
    doing my best to help c2uk's Avatar
    Join Date
    May 2005
    Location
    Cardiff
    Posts
    1,832
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Afaik (and I'm no expert) but those are then three different queries, you can't do a join if there is no relationship between the tables and you can't select data from three different tables without a join

    But it appears you could very well establish a relationship between them, for example the job table could get a field cityID to identify the city the job is located.

    More info on joins: http://www.w3schools.com/sql/sql_join.asp
    Dan G
    Marketing Strategist & Consultant

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rmazin View Post
    select cityName from cities where cityID = '1'
    select userName, userEmail from users where userID = '53'
    select jobName from jobs where jobID='2'

    Is there a way to combine these into one query?
    yup
    Code:
    SELECT 'city'     AS resulttype
         , cityName   AS result1
         , NULL       AS result2    
      FROM cities 
     WHERE cityID = 1
    UNION ALL
    SELECT 'user'
         , userName
         , userEmail 
      FROM users 
     WHERE userID = 53
    UNION ALL
    SELECT 'job'
         , jobName 
         , NULL
      FROM jobs 
     WHERE jobID = 2
    p.s. please don't put quotes around numeric constants being compared to numeric columns
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •