2 tables not joining properly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

2 tables not joining properly

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-01 : 07:08:21

I have tblMembers and tblPics, I have 23 members and 6 pictures for 2 members I want to JOIN the two tables and show all members and the 6 pictures for the 2 members, my code at the moment just shows all members. When I tried a join i think the $offset, $rowsPerPage where messing things up?!

thanks


<?php
include_once("config.php");
doCSS(); ?>
<?php
##### PAGINATION ###########################################
// how many rows to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

$self = $_SERVER['PHP_SELF'];

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
	$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

// how many rows we have in database
$query  = "SELECT COUNT(UserID) AS numrows FROM wmb_members";
$row  = mysql_fetch_assoc(mysql_query($query));
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
	$page = $pageNum - 1;
	$prev = "<a href=\"$self?page=$page\">[Prev]</a>";
	$first = "<a href=\"$self?page=1\">[First Page]</a>";
} 
else
{
	$prev  = '[Prev]';       // we're on page one, don't enable 'previous' link
	$first = '[First Page]'; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
	$page = $pageNum + 1;
	$next = " <a href=\"$self?page=$page\">[Next]</a> ";
	
	$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
	$next = '[Next]';      // we're on the last page, don't enable 'next' link
	$last = '[Last Page]'; // nor 'last page' link
}

##### / PAGINATION ##############################################
// check if form has been submitted


$jq_add_div_strings = '';
$jq_div_classes_csv = '';
$counter = 0;

//startarray

$bikearray = array();

$query  = "SELECT * FROM wmb_members ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

the join I tried, brought back 2 members (the ones with the pictures)


$query  = "SELECT * FROM wmb_members INNER JOIN wmb_pics ON wmb_members.UserID=wmb_pics.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-01 : 07:30:26

You will need to do a LEFT JOIN - see example below , if you place this in SSMS - the Query Window - and execute:
Note:this will return all members and the pictures that are related to the relevant members . If you want something else , could you let me know

CREATE TABLE #wmb_members
(userID INT ,
memname varchar(20)
)

CREATE TABLE #wmb_pics
(userID INT ,
picture varchar(20)
)

INSERT INTO #wmb_members
SELECT 1,'mem1'
UNION
SELECT 2,'mem2'
UNION
SELECT 3,'mem3'

INSERT INTO #wmb_pics
SELECT 1,'pic1'
UNION
SELECT 1,'pic2'
UNION
SELECT 2,'pic1'
UNION
SELECT 2,'pic2'

SELECT * FROM #wmb_members t1
LEFT JOIN #wmb_pics t2 ON t1.userID = t2.userID

DROP TABLE #wmb_pics
DROP TABLE #wmb_members

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-01 : 07:37:36

so basically, I just need this bit:

SELECT * FROM #wmb_members t1
LEFT JOIN #wmb_pics t2 ON t1.userID = t2.userID

will the t1 and t2 bit work in mySQL?

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-01 : 07:49:22

when I do a LEFT JOIN, it only brings back 2 results per page but they are different results?!


$query  = "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-01 : 08:43:17

Try running the query , firstly , in a query window - where you can see the resultset . Once you are happy with the resultset , you can implement it to your app.

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-01 : 09:47:01

where is there a query window? I have navicat Lite

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-01 : 10:10:04

I think there is a SQL Console - on the Lite , but not if you're on Linux .

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-01 : 11:07:09

I can't find a SQL query window/program anywhere?!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 11:45:52

search for mysql workbench in google

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 11:56:41

Or before versions of 5.1 you can use phpmyadmin.

No, you're never too old to Yak'n'Roll if you're too young to die.

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-01 : 12:41:59

ok SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID works but only shows 3 records because of the pagination part

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 12:55:08

If I understand correct then the problem is your PHP-logic to compute the values for $offset and $rowsPerPage.
Maybe you can get better help in a PHP Forum than here in our MS SQL Forum?

No, you're never too old to Yak'n'Roll if you're too young to die.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-02 : 06:18:13

jarv, did you run the query without the PHP specific logic , i.e just as sql on the database ? and did you get the right results?

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

jarv
Posting Yak Master

131 Posts

Posted - 2010-01-02 : 07:05:29

yes the LEFT JOIN works in the query and brings back all results and teh ones with pictures too but when i put it into my page I only get 2 results, the 2 with pictures?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 07:39:49

quote:
Originally posted by jarv

yes the LEFT JOIN works in the query and brings back all results and teh ones with pictures too but when i put it into my page I only get 2 results, the 2 with pictures?!

whats the page logic based on?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-03 : 02:17:24

Strange you're only seeing 2 results , where you have defined in your code to see 10 rows. This looks like a PHP coding problem

Jack Vamvas
--------------------
http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources