Jump to content


Photo

SQL query help


  • Please log in to reply
2 replies to this topic

#1 BigP

BigP
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationIreland

Posted 05 November 2003 - 12:32 AM

Hello!

I\'ve been trying to optimize a few of my queries in a program I wrote but I\'m getting a little lost. Here is a half written SQL query:

  $sql = \'SELECT e.id, e.posterid, e.titletext, m.username\'        . \' FROM phpnews_news e, phpnews_posters m\'        . \' WHERE e.posterid = m.id\'        . \' ORDER BY e.id DESC\';                $SQL_query = mysql_query($sql);                while ($posts = mysql_fetch_array($SQL_query))        {          // Do stuff        }
My tables are like so:

phpnews_news table holds all the news.
phpnews_posters table holds people allowed post news.

The name of the person who posts the news is in the phpnews_posters table. A foreign key, \"posterid\" is used to link them to an article. There\'s also a field called \"postername\" that stores the postersname in the phpnews_news table in case that particular news poster is deleted.

So, as you can see I am able to get the posters name from the phpnews_posters table, but I don\'t know how to use the \"postername\" field from phpnews_news if there is no matching ID in the posters table.

So basically if this isn\'t true:
. \' WHERE e.posterid = m.id\'

Any help is greatly appreciated! :) You\'d be helping me make an Open Sorce project better by helping me here! :)

Thanks!

#2 sirmanson

sirmanson
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationTucson, AZ USA

Posted 13 November 2003 - 07:19 PM

You need to use an outer join : Here is an example

SELECT e.id, e.posterid, e.titletext, m.username
FROM phpnews_news e
LEFT JOIN phpnews_posters m ON e.posterid = m.id
ORDER BY e.id DESC

This will pull all of the data from phpnews_news and only the records in phpnews_posters that match. If you wanted to reverse this (pull all from posters and only those in news that match) you could change it to a RIGHT JOIN

http://www.mysql.com/doc/en/JOIN.html

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 14 November 2003 - 09:25 AM

SELECT e.id, e.posterid, e.titletext, m.username
FROM phpnews_news e
LEFT JOIN phpnews_posters m ON e.posterid = m.id
ORDER BY e.id DESC


You need the name from both tables. If there is a null in the m.username (ie no natching record) then use e.username
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users