Reputation: 3
Read below and navigate to this url http://sqlfiddle.com/#!2/e825f to get a better understanding of my issue.
I have been trying to get this query right for awhile but cannot figure it out. I have been trying to join two tables to get the right data, but maybe I think I should make a third table. See my situation below:
I have two tables:
sites table - lists all websites that I have. See below for how I created my table
create table sites
(
id int,
websiteName varchar(50),
url varchar(50),
mobile_id varchar(50),
is_responsive varchar(1)
);
Mobiles table - lists which websites are mobile and has a mobile url
create table mobile
(
id int,
mobile_url varchar(50)
);
The sites
table and mobiles
table are related through the foreign key in the sites
table called [mobile_id].
The is_responsive column located in the [sites table] is a bit field that holds a 1, stating this site is fully responsive, or a 0 stating this site is non-responsive.
MY GOAL: To build a query that returns all mobile websites and responsive websites. I tried using three tables but that did not work. Originally my third table held all responsive websites, but adding another table does not make sense. Also I found a solution using the UNION statement, but I do not want to use that.
Upvotes: 0
Views: 65
Reputation: 108530
Based on your description of the specification... "all mobile websites and responsive websites"...
I will suggest that something of this form may return the resultset you specified.
SELECT s.id
, s.websiteName
, s.url
, s.mobile_id
, s.is_responsive
, IF(m.mobile_url IS NOT NULL,'1','0') AS is_mobile
FROM sites s
LEFT
JOIN mobile m
ON m.mobile_url = s.mobile_id
WHERE m.mobile_url IS NOT NULL
OR s.is_responsive = '1'
We'd normally expect the foreign key to reference the id column, but given that you say you have a foreign key (and don't specify which column is the target), and the foreign key column has a datatype of VARCHAR(50), we are going to guess that this references the mobile_url
column, since that is the only column in mobile that has a matching datatype.
If the intent is to reference the id
column, then the datatype of the mobile_id
should match the datatype of the id
column, and the join predicate would be:
ON m.id = s.mobile_id
Also, each of your tables should have a PRIMARY KEY, or at least a UNIQUE KEY defined.table.
The table definitions I would use, based on what you posted, would be something along these lines:
CREATE TABLE mobile
( id INT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'PK'
, mobile_url VARCHAR(50) COMMENT 'mobile website URL'
) ;
CREATE TABLE site
( id INT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'PK'
, websiteName VARCHAR(50) COMMENT 'website name'
, url VARCHAR(50) COMMENT 'website URL'
, mobile_id INT UNSIGNED COMMENT 'FK ref mobile.id'
, is_responsive TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'boolean'
, CONSTRAINT FK_site_mobile (mobile_id) REFERENCES mobile(id)
) ;
Upvotes: 1
Reputation: 18183
select *
from sites
left outer join mobile on sites.mobile_id = mobile.id
where sites.is_responsive = '1' or mobile.mobile_url is not null;
Upvotes: 0
Reputation: 1446
The program in the sample is that no mobile site is responsive but the code you wrote first is working.
select sites.* from sites
inner join mobile on sites.mobile_id = mobile.id
where sites.is_responsive = '1'
Upvotes: 0
Reputation: 2189
select mobile_url from from mobile
union
select url from sites when is_responsive = 1
Upvotes: 0
Reputation: 1020
Try something like this
select sites.* from sites
left join mobile on mobile.id = sites.mobile_id
where (sites.is_responsive = '1' or mobile_id is not null)
Upvotes: 1