4 Replies - 4102 Views - Last Post: 09 July 2012 - 01:18 AM

#1 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 928
  • View blog
  • Posts: 3,212
  • Joined: 19-January 10

using column value to select data from other table

Posted 01 July 2012 - 12:36 AM

This is hard to explain so I'll try the best I can. I have two tables. One has a column called status. It's an int. There's another table containing the statuses and their descriptions and such. The first table's column "status" contains the ID for the corresponding status inside of statuses. If I'm selecting data from the first table, is there any way I can include the data from the second table using one query?
Is This A Good Question/Topic? 0
  • +

Replies To: using column value to select data from other table

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: using column value to select data from other table

Posted 01 July 2012 - 04:47 AM

Sounds to me like you're describing a JOIN.
SELECT * 
FROM yourtabe AS y
JOIN statuses AS s
ON y.status = statuses.id;



Of course, that's an inner join, which assumes every row in yourtable has a status value (rows without a matching status will be omitted from the query). If some of the status fields are NULL, then you'd want an outer join.
SELECT * 
FROM yourtabe AS y
LEFT JOIN statuses AS s
ON y.status = statuses.id;


The left join ensures that all rows from yourtable will be included, whether they have a status value or not.
Was This Post Helpful? 3
  • +
  • -

#3 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 928
  • View blog
  • Posts: 3,212
  • Joined: 19-January 10

Re: using column value to select data from other table

Posted 01 July 2012 - 02:56 PM

That is exactly what I needed! I had a feeling join was what I was looking for but I couldn't find anything that described it well enough for me. Thank you!
Was This Post Helpful? 0
  • +
  • -

#4 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 928
  • View blog
  • Posts: 3,212
  • Joined: 19-January 10

Re: using column value to select data from other table

Posted 03 July 2012 - 07:26 PM

So... I'm expermenting around with multiples joins and such. And one thing I found is that it basically just throws everything together. What if I join two tables that contains columns with the same names? Something like:

table a:
id - name - status

table b:
id - name - value

and then the query:
SELECT * FROM a JOIN b ON a.status = b.id;

What if I want to display both name from a and name from b?


EDIT: I figured it out, I can use AS in the query. "SELECT a.name AS 'a_name', b.name AS 'b_name' FROM a JOIN b ON s.status = b.id;"

This post has been edited by creativecoding: 03 July 2012 - 07:51 PM

Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: using column value to select data from other table

Posted 09 July 2012 - 01:18 AM

Only suggestion I would make is to try to get into the habit of using the full name for the particular JOIN. If you use FROM a JOIN b, that is synonymous with FROM a INNER JOIN b. It's an extra 6 characters, but it can make life easier when debugging. The engine considers them identical, there's no difference in performance, so it's not a terrible thing to do.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1