mysql join 4 tables

[mhaselip]

hi, im trying to write a mysql query to join tables.

I basically i have the following tables:

tblwinners = table that shows all the winners PK reward_id customer_id
tbluser = table that holds all the user accounts PK customer_id
tblcompleterewards = completed rewards PK reward_id
tblrewards = holds a list of rewards PK reward_id

I need to output on the winners page a winner which will be in the tblwinners table as a row
Ok I am trying to (select) the username from the tbluser table and select the title from the tblrewards table but ensuring that the rewards_id matches between each table. how can I write a query for me to do this?

[urtrivedi]

post your query u have written so far

[drjohn]

We need a bit more info as to the actual fields in each table before we can help.

Also there is no need to prefix every table name with tbl ! Among other things it gets in the way of reading the actual name.
winners, user, completerewards, rewards - so much more readable and less likely to contain a typo.

[Robert1995]

Please Design you table in MySQL Workbench, I bet most of the people here will really appreciate this and will actually be able to help you.
I will personally come back in one hour, if you provide more information I'll help you out with an explication how to do it and code example.

[mhaselip]

see table structures below:

CREATE TABLE tbluser (
id int(11) NOT NULL auto_increment,
customer_id int(11) default NULL,
username varchar(15) default NULL,
password char(40) NOT NULL default '',
title varchar(4) NOT NULL default '',
first_name varchar(32) default NULL,
last_name varchar(64) default NULL,
night_phone_a varchar(30) default NULL,
night_phone_b varchar(30) default NULL,
email varchar(50) default NULL,
address1 varchar(100) default NULL,
address2 varchar(100) default NULL,
city varchar(40) default NULL,
country varchar(50) default NULL,
zip varchar(32) default NULL,
secret_question varchar(100) default NULL,
secret_answer varchar(100) default NULL,
active int(1) default '1',
qa int(1) NOT NULL default '0',
seckey char(40) default NULL,
referrer int(11) default NULL,
activation_code varchar(40) default NULL,
optin int(1) NOT NULL default '1',
newsletter int(1) NOT NULL default '1',
date_created datetime default NULL,
account_type int(1) NOT NULL default '1',
start_date_membership datetime default NULL,
end_date_membership datetime default NULL,
verify int(1) default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;

CREATE TABLE tblcompleterewards (
id int(11) NOT NULL auto_increment,
reward_id int(11) default NULL,
points int(11) default NULL,
customer_id int(11) default NULL,
link_time datetime default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;

CREATE TABLE tblwinners (
id int(11) NOT NULL auto_increment,
reward_id int(11) default NULL,
customer_id int(11) default NULL,
applied datetime default NULL,
in_stock int(1) default NULL,
dispatched int(1) default '0',
dispatch_date datetime default NULL,
dispatch_type varchar(255) default '2ND CLASS RECORDED',
tracking_no varchar(255) default NULL,
courier_name varchar(255) default 'ROYAL MAIL',
notes text,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE tblrewards (
id int(11) NOT NULL auto_increment,
reward_id int(11) default NULL,
title varchar(50) default NULL,
description text,
qty int(2) default NULL,
points int(11) default NULL,
img1 varchar(100) default NULL,
img2 varchar(50) default NULL,
img3 varchar(50) default NULL,
rrp float(6,2) default NULL,
active int(1) default '1',
pos int(2) default NULL,
start datetime default NULL,
end datetime default NULL,
home_banner_pos int(2) default NULL,
home_banner_img varchar(30) default NULL,
home_banner_active int(1) default NULL,
account_type_access int(1) NOT NULL default '1',
category_id int(11) default NULL,
subcategory_id int(11) default NULL,
min int(4) default NULL,
relisted int(2) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

[urtrivedi]

I was expecting the query u have written so far. Your efforts

[mhaselip]

I've got lots of coppies of different queries so I'm just confused lol

[urtrivedi]

post latest one

[mhaselip]

select tbluser.username, tblrewards.title FROM tbluser, tblrewards LEFT JOIN tblcompleterewards ON tblrewards.reward_id = tblcompleterewards.reward_id LEFT JOIN tblwinners ON tbluser.customer_id = tblwinners.customer_id

this is invalid, dont know why?

[urtrivedi]

This query will list completed awards winner,

select tbluser.username, tblrewards.title 
FROM tblwinners inner join tbluser on tbluser.customer_id = tblwinners.customer_id
inner join tblcompleterewards ON tblwinners.reward_id = tblcompleterewards.reward_id 
inner join tblrewards on tblcompleterewards.reward_id=tblrewards.reward_id

[mhaselip]

oh thats great, apart from the output is shown 7 times, how can i limit this showing only 1 row rather than several rows?

[urtrivedi]

I hope this works

select tbluser.username, tblrewards.title 
FROM tblwinners inner join tbluser on tbluser.customer_id = tblwinners.customer_id

inner join tblcompleterewards ON tblwinners.reward_id = tblcompleterewards.reward_id 
and tblwinners.customer_id = tblcompleterewards.customer_id 

inner join tblrewards on tblcompleterewards.reward_id=tblrewards.reward_id

[mhaselip]

do i just add LIMIT 1 at the end of the query?

[mhaselip]

urtrivedi your 2nd query doesnt work :( retains no rows

[urtrivedi]

what error it gives?
is it syntax error in query or result not proper?

[urtrivedi]

just add distinct to my first query after select word

[mhaselip]

Doesn't error just returns null rows

[urtrivedi]

what is relation between reward and complete reward, how and when rows stored in them

[mhaselip]

reward_id has the same field in both tables. the reward is a table showing all rewards, and completerewards is a list of rewards which users have applied for. so when the reward expires in tblreward php code then randomly selects a user who has applied for the reward and inserts a row into the winners table

[urtrivedi]

my second query failed becaseu your completereward and rblwinners having both rewardid and cust_id, the momement i join both on customer id (only diff between qery1 and query 2), query return null

This is not good way but i add distinct to first query try this

select distinct tbluser.username, tblrewards.title 
FROM tblwinners inner join tbluser on tbluser.customer_id = tblwinners.customer_id
inner join tblcompleterewards ON tblwinners.reward_id = tblcompleterewards.reward_id 
inner join tblrewards on tblcompleterewards.reward_id=tblrewards.reward_id

[mhaselip]

your 1st code did show the correct output but on multiple rows, do we need to add a unique field as tblwinners.rewards_id will be unique, no rewards will share the same reward_id number

[mhaselip]

Yes thats it, you got it :) worked a treat nice 1, thank you for your help :)

is this a bad way of storing data?

[urtrivedi]

try this also

select  tbluser.username, tblrewards.title 
FROM tblwinners inner join tbluser on tbluser.customer_id = tblwinners.customer_id
inner join tblrewards on tblwinners.reward_id=tblrewards.reward_id

[mhaselip]

its not the username that will be unique, as users may win more than 1 reward, its the reward_id thats unique :)

[mhaselip]

just tested it, it seems to show the same user ok with 2 different rewards, i think we can safely say that the query works fine. thanks again :)

[urtrivedi]

u can close this thread now by setting solved

[mhaselip]

your 3rd query worked fine, i will use this and close this thred, much appreciated